from datetime import datetime
from typing import Optional, List

from sqlalchemy import update
from sqlalchemy.exc import IntegrityError, DataError

from app.db import teacher_management_sessionLocal,szh_info_sessionLocal,szh_management_sessionLocal
from app.db.model import get_class_by_tablename
from app.db.model import TeacherDetail, Semester, TeacherData, OtherUserInfo, TeachingGroupSemester, TGPMember
from app.utils.hash_process_pwd import hash_password, verify_password

from app.utils.exceptions import (UserAlreadyExistsError,
                                  InternalServerError, InvalidRoleValueError, UserNotFoundError, PermissionDeniedError,
                                  AuthenticationFailedError, ResourceNotFoundError)

from flask import g

UserInfo = get_class_by_tablename("user_info")
StudentInfo = get_class_by_tablename("student_info")
TeachingGroupDerive = get_class_by_tablename("teaching_group_derive")


def get_current_semester() -> Optional[dict]:
    """
    获取当前学期信息
    :return: {"id": int, "name": str, "syear": str} 或 None
    """
    with szh_management_sessionLocal() as session:
        semester = session.query(Semester).filter(Semester.is_current == True).first()
        if not semester:
            return None
        return {
            "id": semester.id,
            "name": semester.name,
            "syear": semester.syear
        }

def get_current_syear() -> Optional[str]:
    """
    获取当前学年（如 "2024学年"）
    :return: str 或 None
    """
    with szh_info_sessionLocal() as session:
        semester = session.query(Semester).filter(Semester.is_current == True).first()
        return semester.syear if semester else None


def get_teacher_department(user_id: str, syear: str) -> Optional[dict]:
    """
    获取教师在指定学年的部门信息
    :param user_id: 教师 user_id
    :param syear: 学年（如 "2024学年"）
    :return: {"id": str, "name": str, "abbreviation": str} 或 None
    """
    with szh_management_sessionLocal() as session:
        teacher_data = session.query(TeacherData).filter(
            TeacherData.user_id == user_id,
            TeacherData.syear == syear
        ).first()

        if not teacher_data or not teacher_data.department:
            return None

        dept = teacher_data.department
        return {
            "id": dept.id,
            "name": dept.name,
            "abbreviation": dept.abbreviation
        }


def get_teacher_teaching_group(user_id: str, semester_id: int) -> Optional[dict]:
    """
    获取教师在指定学期的教研组信息
    :param user_id: 教师 user_id
    :param semester_id: 学期ID
    :return: {"tgp_semester_id": int, "name": str} 或 None
    """
    with szh_management_sessionLocal() as session:
        member = session.query(TGPMember).join(
            TeachingGroupSemester, TGPMember.tgp_semester_id == TeachingGroupSemester.id
        ).filter(
            TGPMember.user_id == user_id,
            TeachingGroupSemester.semester_id == semester_id
        ).first()

        if not member:
            return None

        group_type = member.group_semester.group_type
        return {
            "tgp_semester_id": member.tgp_semester_id,
            "name": group_type.name if group_type else None,
            "is_leader": member.is_leader,
        }


def get_department_members(department_id: str, syear: str) -> List[dict]:
    """
    获取指定部门在指定学年的所有教师
    :param department_id: 部门ID
    :param syear: 学年（不允许为空）
    :return: [{"user_id": str, "name": str}, ...]
    """
    with szh_management_sessionLocal() as session:
        results = session.query(TeacherData, TeacherDetail).join(
            TeacherDetail, TeacherData.user_id == TeacherDetail.user_id
        ).filter(
            TeacherData.department_id == department_id,
            TeacherData.syear == syear
        ).all()

        return [
            {"user_id": td.user_id, "name": ti.name}
            for td, ti in results
            if ti and ti.name
        ]


def get_teaching_group_members(tgp_semester_id: int) -> List[dict]:
    """
    获取指定教研组学期的所有成员
    :param tgp_semester_id: 教研组学期ID
    :return: [{"user_id": str, "name": str}, ...]
    """
    with szh_management_sessionLocal() as session:
        results = session.query(TGPMember, TeacherDetail).join(
            TeacherDetail, TGPMember.user_id == TeacherDetail.user_id
        ).filter(
            TGPMember.tgp_semester_id == tgp_semester_id
        ).all()

        return [
            {"user_id": tm.user_id, "name": ti.name}
            for tm, ti in results
            if ti and ti.name
        ]


def register_user(
        user_id: str,
        password: str,
        role: str,
        user_info: dict
) -> UserInfo:
    """
    用户注册方法
    :param user_id: 用户id（唯一）
    :param password: 密码
    :param role: 用户角色（默认 guest）
    :param user_info: 补充可空的信息
    :return: 新用户对象
    :raises UserAlreadyExistsError: 用户名已存在或其他校验失败
    """
    # 1. 检查用户名是否存在（唯一性校验）
    with szh_management_sessionLocal() as session:
        if session.query(UserInfo).filter(UserInfo.user_id == user_id).first():
            raise UserAlreadyExistsError(user_id)

        try:
            password_hash = hash_password(password)
            # 3. 创建用户对象
            new_user = UserInfo(
                user_id=user_id,
                password=password_hash,
                role=role,
                name=user_info.get("name", None),
                # 其他字段自动生成：
                # id = uuid4 (由模型默认值处理)
            )

            # 4. 提交到数据库
            session.add(new_user)
            session.commit()
            session.refresh(new_user)  # 刷新获取数据库生成的字段（如时间戳）
            return new_user

        except IntegrityError as e:
            # 处理并发场景下的唯一键冲突（尽管已预先检查）
            session.rollback()
            raise InternalServerError({"u-facingMsg": "用户注册失败，请重试"}) from e
        except DataError as de:
            session.rollback()
            # 解析数据库错误信息
            if "role" in str(de.orig):
                raise InvalidRoleValueError({"u-facingMsg": f"invalid_value: {role}"}) from de
            else:
                raise  # 其他数据库错误继续抛出


def get_acc_info(user_id: str):
    if user_id != g.user_id:
        raise PermissionDeniedError(f"/auth/me?user_id={user_id}", g.user_id)
    with szh_management_sessionLocal() as session:
        user = session.query(UserInfo).filter(UserInfo.user_id == user_id).first()
        if not user:
            raise UserNotFoundError(user_id)

        role = user.role
        base_info = {
            "user_id": user.user_id,
            "role": user.role,
            "status": user.status,
        }

    if role == "student":
        student_info = session.query(StudentInfo).filter(StudentInfo.student_id == user_id).first()
        if not student_info:
            raise ResourceNotFoundError("学生信息", user_id, {"u-facingMsg": "学生信息不存在"})
        # 获取专业和系部名称
        major_name = student_info.major.name if student_info.major else None
        department_name = student_info.major.department.name if student_info.major and student_info.major.department else None

        return {
            **base_info,
            "name": student_info.name,
            "class": student_info.class_name,
            "major": major_name,
            "department": department_name,
            "grade": student_info.grade,
        }
    elif role in ["teacher"]:
        teacher = session.query(TeacherDetail).filter(TeacherDetail.user_id == user_id).first()
        if not teacher:
            raise InternalServerError({"u-facingMsg": "教师身份信息缺失"})

        current_semester = get_current_semester()
        if not current_semester:
            raise InternalServerError({"u-facingMsg": "未设置当前学期"})

        department_info = get_teacher_department(user_id, current_semester["syear"])
        tgp_info = get_teacher_teaching_group(user_id, current_semester["id"])

        return {
            **base_info,
            "name": teacher.name,
            "department": department_info["name"] if department_info else None,
            "teaching_group": tgp_info["name"] if tgp_info else None,
            "is_group_leader": tgp_info["is_leader"] if tgp_info else False,
            "syear": current_semester["syear"],
            "semester": current_semester["name"],
        }
    elif role in ["admin", "guest", "dev_operator"]:
        # 其他角色尝试从 OtherUserInfo 获取 name
        other = session.query(OtherUserInfo).filter(OtherUserInfo.user_id == user_id).first()
        name = other.name if other else "未配表"  # 用 user_id 作为兜底

        return {
            **base_info,
            "name": name,
        }
    else:
        return {}


def update_user_password(user_id: str, old_password: str, new_password: str):
    """
    根据 user_id 更新用户密码

    :param user_id: 要修改密码的用户ID
    :param old_password: 旧密码（未加密的字符串）
    :param new_password: 新密码（未加密的字符串）
    :return: 是否成功更新
    """
    # 使用核心更新方式，效率更高
    with szh_management_sessionLocal() as session:
        try:
            user = session.query(UserInfo).filter_by(user_id=user_id).first()
            if not user:
                raise AuthenticationFailedError({"u-facingMsg": "用户名或密码错误", "attempted_credential": user_id})

            if not verify_password(user.password, old_password):
                raise AuthenticationFailedError({"u-facingMsg": "用户名或密码错误", "attempted_credential": user_id})

            # 使用 SQLAlchemy Core 的 update 语句
            stmt = update(UserInfo).where(UserInfo.user_id == user_id).values(
                password=hash_password(new_password))
            result = session.execute(stmt)
            session.commit()

            if result.rowcount > 0:
                return {"success": True, "message": "密码更新成功"}
            else:
                raise InternalServerError({"u-facingMsg": "用户修改密码失败，请重试"})

        except AuthenticationFailedError as e:
            raise
        except Exception as e:
            raise InternalServerError({"u-facingMsg": "用户修改密码失败，请重试"}) from e


def admin_reset_password(target_user_id: str, new_password: str = "123456", operator: str = "system"):
    """
    管理员重置用户密码（无需旧密码）

    :param target_user_id: 目标用户 user_id
    :param new_password: 新密码，默认为 "123456"
    :param operator: 操作人 user_id（用于日志）
    :return: dict
    """
    with szh_management_sessionLocal() as session:
        user = session.query(UserInfo).filter(UserInfo.user_id == target_user_id).first()
        if not user:
            raise UserNotFoundError(target_user_id)

        # todo: 记录操作日志（应写入数据库）
        # log_action(operator, "admin_reset_password", target_user_id)

        # 更新密码
        user.password = hash_password(new_password)
        session.commit()

    return {
        "success": True,
        "message": f"用户 {target_user_id} 的密码已重置",
        "reset_by": operator
    }